package com.example.springboot.mapper;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.springboot.entity.*;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;

import java.util.List;
import java.util.Map;

@Mapper
//查询所有媒尘数据
public interface McDustMapper {
    @Select("SELECT * FROM mc_2020 " +
            "UNION ALL " +
            "SELECT * FROM mc_2021 " +
            "UNION ALL " +
            "SELECT * FROM mc_2022 " +
            "UNION ALL " +
            "SELECT * FROM mc_2023")
    List<McDust> selectAllMc();

    //    查询所有水泥数据
    @Select("SELECT * FROM sn_2020 " +
            "UNION ALL " +
            "SELECT * FROM sn_2021 " +
            "UNION ALL " +
            "SELECT * FROM sn_2022 " +
            "UNION ALL " +
            "SELECT * FROM sn_2023")
    List<McDust> selectAllSn();

    //    查询所有矽尘数据
    @Select("SELECT * FROM xc_2020 " +
            "UNION ALL " +
            "SELECT * FROM xc_2021 " +
            "UNION ALL " +
            "SELECT * FROM xc_2022 " +
            "UNION ALL " +
            "SELECT * FROM xc_2023")
    List<McDust> selectAllXc();

    // 查询所有粉尘数据
    @Select("SELECT * FROM mc_2020 " +
            "UNION ALL " +
            "SELECT * FROM mc_2021 " +
            "UNION ALL " +
            "SELECT * FROM mc_2022 " +
            "UNION ALL " +
            "SELECT * FROM mc_2023 " +
            "UNION ALL " +
            "SELECT * FROM sn_2020 " +
            "UNION ALL " +
            "SELECT * FROM sn_2021 " +
            "UNION ALL " +
            "SELECT * FROM sn_2022 " +
            "UNION ALL " +
            "SELECT * FROM sn_2023 " +
            "UNION ALL " +
            "SELECT * FROM xc_2020 " +
            "UNION ALL " +
            "SELECT * FROM xc_2021 " +
            "UNION ALL " +
            "SELECT * FROM xc_2022 " +
            "UNION ALL " +
            "SELECT * FROM xc_2023")
    List<McDust> selectAllDust();


    //    按地点模糊查询某个粉尘数据
    @Select("select * from mc_2020 where city_name like CONCAT('%', #{city_name}, '%') " +
            "union all " +
            "select * from mc_2021 where city_name like CONCAT('%', #{city_name}, '%') " +
            "union all " +
            "select * from mc_2022 where city_name like CONCAT('%', #{city_name}, '%') " +
            "union all " +
            "select * from mc_2023 where city_name like CONCAT('%', #{city_name}, '%')")
    List<McDust> selectMcByCity(String city_name);

    //    按地点模糊查询某个水泥数据
    @Select("select * from sn_2020 where city_name like CONCAT('%', #{city_name}, '%') " +
            "union all " +
            "select * from sn_2021 where city_name like CONCAT('%', #{city_name}, '%') " +
            "union all " +
            "select * from sn_2022 where city_name like CONCAT('%', #{city_name}, '%') " +
            "union all " +
            "select * from sn_2023 where city_name like CONCAT('%', #{city_name}, '%')")
    List<McDust> selectSnByCity(String city_name);

    //    按地点模糊查矽尘数据
    @Select("select * from xc_2020 where city_name like CONCAT('%', #{city_name}, '%') " +
            "union all " +
            "select * from xc_2021 where city_name like CONCAT('%', #{city_name}, '%') " +
            "union all " +
            "select * from xc_2022 where city_name like CONCAT('%', #{city_name}, '%') " +
            "union all " +
            "select * from xc_2023 where city_name like CONCAT('%', #{city_name}, '%')")
    List<McDust> selectXcByCity(String city_name);

//    按照年份查询某一个年份表或者2个年份表
//    @Select("select * from mc_${year} " +
//            "union all " +
//            "select * from sn_${year} " +
//            "union all " +
//            "select * from xc_${year}")
//    List<McDust> selectDustByYear(@Param("year") int year);


    //  查询出某一年的媒尘数据
    @SelectProvider(type = McDustSqlProvider.class, method = "selectMcByYear")
    List<McDust> selectMcByYear(@Param("year") int year, @Param("city_name") String city_name);


    //  查询出某一年的水泥数据
    @SelectProvider(type = McDustSqlProvider.class, method = "selectSnByYear")
    List<McDust> selectSnByYear(@Param("year") int year, @Param("city_name") String city_name);


    //  查询出某一年的矽尘数据
    @SelectProvider(type = McDustSqlProvider.class, method = "selectXcByYear")
    List<McDust> selectXcByYear(@Param("year") int year, @Param("city_name") String city_name);

    //    查询某一年的媒尘数据
    @Select("select * from mc_${year} " )
    List<McDust> selectMcDustByYear(@Param("year") int year);

    //    查询某一年的水泥数据
    @Select("select * from sn_${year} ")
    List<McDust> selectSnDustByYear(@Param("year") int year);

    //    查询某一年的矽尘数据
    @Select("select * from xc_${year} ")
    List<McDust> selectXcDustByYear(@Param("year") int year);

    //    查询华昊体检数据
    @Select("SELECT * FROM physical_examination ")
    List<PhysicalExamination> selectAllHh();

    //    查询大众体检数据
    @Select("SELECT * FROM physical_nxdz ")
    List<PhysicalNxdz> selectAllDz();

    //    查询青龙体检数据
    @Select("SELECT * FROM physical_qinglong ")
    List<PhysicalQinglong> selectAllQingLong();


    //    查询银川栏劫体检数据
    @Select("SELECT * FROM physical_yclanjie ")
    List<PhysicalYclanjie> selectAllYinchuan();


    //    查询一品生物体检数据
    @Select("SELECT * FROM physical_yiping ")
    List<PhysicalYiping> selectAllYpsw();

    // 新增的通用模糊查询方法
//    在议
//    @SelectProvider(type = McDustSqlProvider.class, method = "selectAllDustWithConditions")
//    Page<McDust> selectAllDustWithConditions(@Param("page") Page<McDust> page, @Param("tables") List<String> tables, @Param("conditions") Map<String, Object> conditions);

}